﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace FinalProject
{
    public class Utility
    {
        public static ClsConnection CConnection;
        public static SqlCommand sqlCommand;
        private static UserInformation userCurrent;

        public static UserInformation UserCurrent
        {
            get { return userCurrent; }
            set { userCurrent = value; }
        }

        #region System data        
        public static List<RootObject> LstTinhThanh;
        public static List<RootObject> LstQuocGia;
        public static List<RootObject> LstTinhTrangHonNhan;
        public static List<RootObject> LstQuanHuyen;
        public static List<RootObject> LstTruongHoc;
        public static List<RootObject> LstBangCap;
        public static List<RootObject> LstChucVu;
        public static List<RootObject> LstNganhNghe;
        public static List<RootObject> LstNgoaiNgu;
        public static List<RootObject> LstTrinhDoNgoaiNgu;
        public static List<RootObject> LstChuyenNganh;
        public static List<CongViec> LstCongViec;
        public static List<CongViec> LstCongViecCurrent;

        public static int indexThongTinThamKhao;
        public static int indexKinhNghiemLamViec;
        public static int indexHocVanBangCap;
        public static int indexHoSo;

        private static int iHoSoID = 0;
        private static int iKinhNghiemLamViecID = 0;
        private static int iHocVanBangCapID = 0;
        private static int iThongTinThamKhaoID = 0;

        public static HoSo HoSoCurrent;
        #endregion       
		
        public static void Init()
        {
            CConnection = new ClsConnection();
            CConnection.openConnection();
            sqlCommand = CConnection.getSqlCommand();

            LstTinhThanh = GetList("LSProvinceID", "VNName", "LS_tblProvince");
            LstQuocGia = GetList("LSNationalityID", "VNName", "LS_tblNationality");
            LstTinhTrangHonNhan = GetList("LSMaritalID", "VNName", "LS_tblMarital");
            LstQuanHuyen = new List<RootObject>();
            LstTruongHoc = GetList("LSSchoolID", "VNName", "LS_tblSchool");
            LstBangCap = GetList("LSQualifiTypeID", "VNName", "LS_tblQualifiType");
            LstChucVu = GetList("LSChucVuID", "TenChucVuV", "LS_tblChucVu");
            LstNganhNghe = GetList("LSJobTitleID", "VNName", "LS_tblJobTitle");
            LstNgoaiNgu = GetList("LSLanguageID", "VNName", "LS_tblLanguage");
            LstTrinhDoNgoaiNgu = GetList("LSLanguageLevelID", "VNName", "LS_tblLanguageLevel");
            LstChuyenNganh = GetList("LSProfessionalLevelID", "VNName", "LS_tblProfessionalLevel");
            LstCongViec = GetListCongViec();
            LstCongViecCurrent = GetListCongViec();

            HoSoCurrent = new HoSo();

            indexThongTinThamKhao = -1;
            indexKinhNghiemLamViec = -1;
            indexHocVanBangCap = -1;
            indexHoSo = -1;
        }

        public static void setProvinceDropDownlist(DropDownList ddlProvince)
        {

            string[] arrStrProvince = {"Hồ Chí Minh", "Hà Nội", "ĐBSCL", "An Giang", "Bà Rịa - Vũng Tàu", "Bắc cạn",
                                          "Bắc Giang", "Bạc Liêu", "Bắc Ninh", "Bến Tre", "Biên Hòa",
                                          "Bình Định","Bình Dương","Bình Phước","Bình Thuận","Cà Mau","Cần Thơ",
                                          "Cao Bằng","Đà Nẵng","Đắc Lắc","Điện Biên","Đồng Nai","Đồng Tháp",
                                          "Gia Lai","Hà Giang","Hà Nam","Hà Tây","Hà Tĩnh","Hải Dương","Hải Phòng",
                                          "Hòa Bình","Huế","Hưng Yên","Khánh Hòa","Kon Tum","Lai Châu","Lâm Đồng","Lạng Sơn",
                                          "Lào Cai","Long An","Nam Định","Nghệ An","Ninh Bình","Ninh Thuận","Phú Thọ",
                                          "Phú Yên","Quảng Bình","Quảng Nam","Quảng Ngãi","Quảng Ninh","Quảng Trị",
                                          "Sóc Trăng","Sơn La","Tây Ninh","Thái Bình","Thái Nguyên","Thanh Hóa","Thừa Thiên-Huế",
                                          "Tiền Giang","Trà Vinh","Tuyên Quang","Kiên Giang","Vĩnh Long","Vĩnh Phúc","Yên Bái",
                                          "Khác","Quốc tế","Hậu Giang"};
            foreach (string str in arrStrProvince)
            {
                ddlProvince.Items.Add(str);
            }
        }

        public static void UpdateUser()
        {
            try
            {

            }
            catch { }
        }

        #region Get List        
        public static List<RootObject> GetList(string strCol1, string strCol2, string strTable)
        {
            try
            {
                sqlCommand.CommandText = "SELECT " + strCol1 + "," + strCol2 + " FROM " + strTable;
                SqlDataReader reader = sqlCommand.ExecuteReader();

                List<RootObject> lst = new List<RootObject>();
                while (reader.Read())
                {
                    RootObject obj = new RootObject(reader[0].ToString(), reader[1].ToString());
                    lst.Add(obj);
                }
                reader.Close();

                return lst;
            }
            catch
            {
                Console.Write("Loi");
                return null;
            }
        }                

        public static List<RootObject> GetQuanHuyenList(string provinceID)
        {
            try
            {                                
                sqlCommand.CommandText = "SELECT LSDistrictID,VNName FROM LS_tblDistrict WHERE LSProvinceID='" + provinceID + "'";
                SqlDataReader reader = sqlCommand.ExecuteReader();

                List<RootObject> lst = new List<RootObject>();
                while (reader.Read())
                {
                    RootObject quanHuyen = new RootObject(reader[0].ToString(), reader[1].ToString());
                    lst.Add(quanHuyen);
                }
                reader.Close();

                return lst;
            }
            catch
            {
                Console.Write("Loi");
                return null;
            }
        }

        public static List<CongViec> GetListCongViec()
        {            
            try
            {
                sqlCommand.CommandText = "SELECT LSJobTitleID,VNName,GioiThieu,MoTa,YeuCau,NguoiLienHe,NoiLamViecID,LSChucVuID,NgonNguID,NgayBD,NgayKT FROM LS_tblJobTitle";
                SqlDataReader reader = sqlCommand.ExecuteReader();

                List<CongViec> lst = new List<CongViec>();
                while (reader.Read())
                {
                    DateTime ngayBD;
                    DateTime ngayKT;
                    try
                    {
                        ngayBD = DateTime.Parse(reader[9].ToString());
                    }
                    catch { ngayBD = DateTime.Today; }

                    try
                    {
                        ngayKT = DateTime.Parse(reader[10].ToString());
                    }
                    catch { ngayKT = DateTime.Today; }
                        
                    CongViec congViec = new CongViec(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(),
                        reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(),
                        reader[8].ToString(), ngayBD, ngayKT);
                    lst.Add(congViec);
                }
                reader.Close();

                return lst;
            }
            catch
            {
                Console.Write("Loi");
                return null;
            }            
        }
        #endregion

        public static void LoadDropDownList(ref DropDownList ddl, List<RootObject> lst)
        {            
            try
            {
                ddl.Items.Clear();
                ddl.Items.Add("Vui lòng chọn...");
                foreach (RootObject obj in lst)
                {
                    ddl.Items.Add(obj.Ten);
                }
                ddl.SelectedIndex = 0;
            }
            catch
            {
            }
        }
             
        public static void DisplayAlert(string strMessage, string strURL)
        {
            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=\"JavaScript\">alert(\"" + strMessage + "\"); window.location = \"" + strURL + "\"; </SCRIPT>");
        }

        public static void DisplayAlert(string strMessage)
        {
            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=\"JavaScript\">alert(\"" + strMessage + "\");</SCRIPT>");
        }

        #region Get Object by ID or Ten
        public static RootObject getNganhNgheByID(string strID)

        {
            for (int i = 0; i < LstNganhNghe.Count; i++)
            {
                if (strID.CompareTo(LstNganhNghe[i].ID.ToString()) == 0)
                {
                    return new RootObject(LstNganhNghe[i].ID, LstNganhNghe[i].Ten);
                }
            }
            return null;
        }

        public static NganhNghe getNganhNgheNNByID(string strID)
        {
            for (int i = 0; i < LstNganhNghe.Count; i++)
            {
                if (strID.CompareTo(LstNganhNghe[i].ID.ToString()) == 0)
                {
                    return new NganhNghe(LstNganhNghe[i].ID, LstNganhNghe[i].Ten);
                }
            }
            return null;
        }


        public static RootObject getNganhNgheByTen(string strTen)
        {
            for (int i = 0; i < LstNganhNghe.Count; i++)
            {
                if (strTen.CompareTo(LstNganhNghe[i].Ten.ToString()) == 0)
                {
                    return new RootObject(LstNganhNghe[i].ID, LstNganhNghe[i].Ten);
                }
            }
            return null;
        }

        public static NganhNghe getNganhNgheNNByTen(string strTen)
        {
            for (int i = 0; i < LstNganhNghe.Count; i++)
            {
                if (strTen.CompareTo(LstNganhNghe[i].Ten.ToString()) == 0)
                {
                    return new NganhNghe(LstNganhNghe[i].ID, LstNganhNghe[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getTinhThanhByID(string strID)
        {
            for (int i = 0; i < LstTinhThanh.Count; i++)
            {
                if (strID.CompareTo(LstTinhThanh[i].ID.ToString()) == 0)
                {
                    return new RootObject(LstTinhThanh[i].ID, LstTinhThanh[i].Ten);
                }
            }
            return null;
        }

        public static TinhThanh getTinhThanhTTByID(string strID)
        {
            for (int i = 0; i < LstTinhThanh.Count; i++)
            {
                if (strID.CompareTo(LstTinhThanh[i].ID.ToString()) == 0)
                {
                    return new TinhThanh(LstTinhThanh[i].ID, LstTinhThanh[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getTinhThanhByTen(string strTen)
        {
            for (int i = 0; i < LstTinhThanh.Count; i++)
            {
                if (strTen.CompareTo(LstTinhThanh[i].Ten.ToString()) == 0)
                {
                    return new RootObject(LstTinhThanh[i].ID, LstTinhThanh[i].Ten);
                }
            }
            return null;
        }

        public static TinhThanh getTinhThanhTTByTen(string strTen)
        {
            for (int i = 0; i < LstTinhThanh.Count; i++)
            {
                if (strTen.CompareTo(LstTinhThanh[i].Ten.ToString()) == 0)
                {
                    return new TinhThanh(LstTinhThanh[i].ID, LstTinhThanh[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getQuocGiaByID(string strID)
        {
            for (int i = 0; i < LstQuocGia.Count; i++)
            {
                if (strID.CompareTo(LstQuocGia[i].ID) == 0)
                {
                    return new RootObject(LstQuocGia[i].ID, LstQuocGia[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getTinhTrangHonNhan(string strID)
        {
            for (int i = 0; i < LstTinhTrangHonNhan.Count; i++)
            {
                if (strID.CompareTo(LstTinhTrangHonNhan[i].ID) == 0)
                {
                    return new RootObject(LstTinhTrangHonNhan[i].ID, LstTinhTrangHonNhan[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getQuanHuyenByID(string strID)
        {
            for (int i = 0; i < LstQuanHuyen.Count; i++)
            {
                if (strID.CompareTo(LstQuanHuyen[i].ID) == 0)
                {
                    return new RootObject(LstQuanHuyen[i].ID, LstQuanHuyen[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getBangCapByID(string strID)
        {
            for (int i = 0; i < LstBangCap.Count; i++)
            {
                if (strID.CompareTo(LstBangCap[i].ID) == 0)
                {
                    return new RootObject(LstBangCap[i].ID, LstBangCap[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getTruongByID(string strID)
        {
            for (int i = 0; i < LstTruongHoc.Count; i++)
            {
                if (strID.CompareTo(LstTruongHoc[i].ID) == 0)
                {
                    return new RootObject(LstTruongHoc[i].ID, LstTruongHoc[i].Ten);
                }
            }
            return null;
        }

        public static RootObject getChuyenNganhByID(string strID)
        {
            for (int i = 0; i < LstChuyenNganh.Count; i++)
            {
                if (strID.CompareTo(LstChuyenNganh[i].ID) == 0)
                {
                    return new RootObject(LstChuyenNganh[i].ID, LstChuyenNganh[i].Ten);
                }
            }
            return null;
        }

        public static ChucVu getChucVuByID(string strID)
        {
            for (int i = 0; i < LstChucVu.Count; i++)
            {
                if (strID.CompareTo(LstChucVu[i].ID) == 0)
                {
                    return new ChucVu(LstChucVu[i].ID, LstChucVu[i].Ten);
                }
            }
            return null;
        }

        public static NgoaiNgu getNgoaiNguByID(string strID)
        {
            for (int i = 0; i < LstNgoaiNgu.Count; i++)
            {
                if (strID.CompareTo(LstNgoaiNgu[i].ID) == 0)
                {
                    return new NgoaiNgu(LstNgoaiNgu[i].ID, LstNgoaiNgu[i].Ten);
                }
            }
            return null;
        }

        public static TrinhDoNgoaiNgu getTrinhDoNgoaiNguByID(string strID)
        {
            for (int i = 0; i < LstTrinhDoNgoaiNgu.Count; i++)
            {
                if (strID.CompareTo(LstTrinhDoNgoaiNgu[i].ID) == 0)
                {
                    return new TrinhDoNgoaiNgu(LstTrinhDoNgoaiNgu[i].ID, LstTrinhDoNgoaiNgu[i].Ten);
                }
            }
            return null;
        }


        public static void getThongTinID()
        {
            try
            {
                sqlCommand.CommandText = "SELECT ValueHoSo, ValueKinhNghiemLamViec, ValueHocVanBangCap, ValueThongTinThamKhao FROM tblIndex";
                SqlDataReader dataReader = sqlCommand.ExecuteReader();
                while (dataReader.Read())
                {
                    iHoSoID = (int)dataReader[0] + 1;
                    iKinhNghiemLamViecID = (int)dataReader[1] + 1;
                    iHocVanBangCapID = (int)dataReader[2] + 1;
                    iThongTinThamKhaoID = (int)dataReader[3] + 1;
                }

                dataReader.Close();
            }
            catch (Exception ex)
            {
            }              
        }

        public static CongViec getCongViecByID(string strID)
        {
            for (int i = 0; i < LstCongViec.Count; i++)
            {
                if (LstCongViec[i].ID == strID)
                    return LstCongViec[i];
            }
            return null;
        }
        #endregion

        #region Get Ten by ID
        public static string GetTenRootObject(List<RootObject> lst, string ID)
        {
            for (int i = 0; i < lst.Count; i++)
            {
                if (lst[i].ID == ID)
                    return lst[i].Ten;
            }

            return "";
        }

        public static string GetIDRootObject(List<RootObject> lst, string Ten)
        {
            for (int i = 0; i < lst.Count; i++)
            {
                if (lst[i].Ten == Ten)
                    return lst[i].ID;
            }

            return "";
        }
        #endregion

        #region HoSo
        public static int insertThongTinHoSoUser()
        {
            try
            {
                getThongTinID();
                ThongTinLienHe a = HoSoCurrent.ThongTinLienHe;

                sqlCommand.CommandText = String.Format(@"insert into tblHoSoUngVien(
                ID, LastName, FirstName, NgaySinh, GioiTinh, TinhTrangHonNhanID, DiaChi, 
                QuocGiaID, TinhThanhID, QuanHuyenID, SDT, Image, EmpID, SoNamKinhNghiem,
                ViTriMongMuon, ChucVuID, NgoaiNgu1ID, NgoaiNgu2ID, NgoaiNgu3ID, TrinhDoNN1ID,
                TrinhDoNN2ID, TrinhDoNN3ID, NoiLamViec1ID, NoiLamViec2ID, NoiLamViec3ID,
                NganhNghe1ID, NganhNghe2ID, NganhNghe3ID, MucLuong, IsChoPhepTimKiem,
                MucTieuNgheNghiep, ThanhTichNoiBat, KyNangNoiBat, NgayCapNhatGanNhat) 
                 values(N'{0}', N'{1}', N'{2}', '{3}', '{4}', N'{5}', N'{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', {13}, N'{14}', '{15}', {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}, '{29}', N'{30}', N'{31}', N'{32}', '{33}')",
                    iHoSoID, a.HoVaLot, a.Ten, a.NgaySinh.ToString("M/d/yyyy"), a.GioiTinh.ToString(), a.TinhTrangHonNhan.ID, a.DiaChi,
                  a.QuocGia.ID, a.TinhThanh.ID, a.QuanHuyen.ID, a.SDT, a.PathImage, userCurrent.StrEmpID, HoSoCurrent.NamKinhNghiem,
                  HoSoCurrent.ViTriMongMuon, HoSoCurrent.ChucVu.ID,
                  HoSoCurrent.LstNgoaiNgu.Count <= 0 ? "null" : "'" + HoSoCurrent.LstNgoaiNgu[0].ID + "'",
                  HoSoCurrent.LstNgoaiNgu.Count <= 1 ? "null" : "'" + HoSoCurrent.LstNgoaiNgu[1].ID + "'",
                  HoSoCurrent.LstNgoaiNgu.Count <= 2 ? "null" : "'" + HoSoCurrent.LstNgoaiNgu[2].ID + "'",
                  HoSoCurrent.LstNgoaiNgu.Count <= 0 ? "null" : "'" + HoSoCurrent.LstTrinhDoNgoaiNgu[0].ID + "'",
                  HoSoCurrent.LstNgoaiNgu.Count <= 1 ? "null" : "'" + HoSoCurrent.LstTrinhDoNgoaiNgu[1].ID + "'",
                  HoSoCurrent.LstNgoaiNgu.Count <= 2 ? "null" : "'" + HoSoCurrent.LstTrinhDoNgoaiNgu[2].ID + "'",
                  HoSoCurrent.LstNoiLamViec.Count <= 0 ? "null" : "'" + HoSoCurrent.LstNoiLamViec[0].ID + "'",
                  HoSoCurrent.LstNoiLamViec.Count <= 1 ? "null" : "'" + HoSoCurrent.LstNoiLamViec[1].ID + "'",
                  HoSoCurrent.LstNoiLamViec.Count <= 2 ? "null" : "'" + HoSoCurrent.LstNoiLamViec[2].ID + "'",
                  HoSoCurrent.LstNganhNghe.Count <= 0 ? "null" : "'" + HoSoCurrent.LstNganhNghe[0].ID + "'",
                  HoSoCurrent.LstNganhNghe.Count <= 1 ? "null" : "'" + HoSoCurrent.LstNganhNghe[1].ID + "'",
                  HoSoCurrent.LstNganhNghe.Count <= 2 ? "null" : "'" + HoSoCurrent.LstNganhNghe[2].ID + "'",
                  HoSoCurrent.MucLuong, HoSoCurrent.IsChoPhepTimKiem,
                  HoSoCurrent.MucTieuNgheNghiep, HoSoCurrent.ThanhTichNoiBat, HoSoCurrent.KyNangNoiBat, DateTime.Now.ToString("M/d/yyyy"));

                return sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int insertHocVanBangCap()
        {
            try
            {
                List<HocVanBangCap> a = HoSoCurrent.LstHocVanBangCap;
                for (int indexHVBC = 0; indexHVBC < a.Count; indexHVBC++)
                {
                    sqlCommand.CommandText = String.Format(@"insert into tblHocVanBangCap(
                        ID, HoSoID, BangCapID, TruongID, ChuyenNganhID, NgayBatDau, NgayKetThuc, ThongTinLienQuan) 
                         values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', N'{7}')",
                            iHocVanBangCapID++, iHoSoID, a[indexHVBC].BangCap.ID, a[indexHVBC].Truong.ID, a[indexHVBC].ChuyenNganh.ID,
                            a[indexHVBC].NgayBD.ToString("M/d/yyyy"), a[indexHVBC].NgayKT.ToString("M/d/yyyy"), a[indexHVBC].ThongTinLienQuan);
                    sqlCommand.ExecuteNonQuery();
                }

                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int insertKinhNghiemLamViec()
        {
            try
            {
                List<KinhNghiemLamViec> a = HoSoCurrent.LstKinhNghiemLamViec;
                for (int indexKNLV = 0; indexKNLV < a.Count; indexKNLV++)
                {
                    sqlCommand.CommandText = String.Format(@"insert into tblKinhNghiemLamViec(
                        ID, HoSoID, ChucDanh, CongTy, ChucVuID, NganhNgheID, NgayBatDau, NgayKetThuc, NhiemVuThanhTich) 
                        values('{0}', '{1}', N'{2}', N'{3}', '{4}', '{5}', '{6}', '{7}', N'{8}')",
                        iKinhNghiemLamViecID++, iHoSoID, a[indexKNLV].ChucDanh, a[indexKNLV].CongTy, a[indexKNLV].ChucVu.ID,
                        a[indexKNLV].NganhNghe.ID, a[indexKNLV].NgayBD.ToString("M/d/yyyy"), a[indexKNLV].NgayKT.ToString("M/d/yyyy"), a[indexKNLV].NhiemVuVaThanhTich);
                    sqlCommand.ExecuteNonQuery();
                }
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int insertThongTinThamKhao()
        {
            try
            {
                List<ThongTinThamKhao> a = HoSoCurrent.LstThongTinThamKhao;
                for (int indexTTTK = 0; indexTTTK < a.Count; indexTTTK++)
                {
                    sqlCommand.CommandText = String.Format(@"insert into tblThongTinThamKhao(
                        ID, HoSoID, HoTen, ChucDanh, CongTy, Email, SDT) 
                         values('{0}', '{1}', N'{2}', N'{3}', N'{4}', '{5}', '{6}')",
                        iThongTinThamKhaoID++, iHoSoID, a[indexTTTK].HoTen, a[indexTTTK].ChucDanh, a[indexTTTK].CongTy, a[indexTTTK].Email, a[indexTTTK].SDT);
                    sqlCommand.ExecuteNonQuery();
                }
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static void insertHoSoUserFull()
        {
            try
            {
                int result1 = insertThongTinHoSoUser();
                int result2 = insertHocVanBangCap();
                int result3 = 0;
                if (HoSoCurrent.LstKinhNghiemLamViec != null)
                    result3 = insertKinhNghiemLamViec();
                int result4 = 0;
                if (HoSoCurrent.LstThongTinThamKhao != null)
                    result4 = insertThongTinThamKhao();

                sqlCommand.CommandText = String.Format(@"update tblIndex set ValueHoSo = {0}, ValueKinhNghiemLamViec = {1}, 
             ValueHocVanBangCap = {2}, ValueThongTinThamKhao = {3}", iHoSoID, iKinhNghiemLamViecID, iHocVanBangCapID, iThongTinThamKhaoID);
                int result5 = sqlCommand.ExecuteNonQuery();
                userCurrent.LstHoSo.Add(HoSoCurrent);
            }
            catch (Exception ex)
            {
                
            }
        }

        public static bool deleteHoSo(string idHoSo)
        {
            try
            {
                sqlCommand.CommandText = "DELETE FROM tblHoSoUngVien WHERE ID = '" + idHoSo + "'";
                int result1 = sqlCommand.ExecuteNonQuery();
                sqlCommand.CommandText = "delete from tblKinhNghiemLamViec where HoSoID = '" + idHoSo + "'";
                int result2 = sqlCommand.ExecuteNonQuery();
                sqlCommand.CommandText = "delete from tblHocVanBangCap where HoSoID = '" + idHoSo + "'";
                int result3 = sqlCommand.ExecuteNonQuery();

                sqlCommand.CommandText = "delete from tblThongTinThamKhao where HoSoID = '" + idHoSo + "'";
                int result4 = sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static bool updateHoSo(HoSo hoso)
        {
            try
            {
                getThongTinID();
                string idHoSoUpdate = hoso.ID;
                //string idHoSoUpdate = id;
                 List<KinhNghiemLamViec> lstKNLV = new List<KinhNghiemLamViec>();
                List<HocVanBangCap> lstHVBC = new List<HocVanBangCap>();
                List<ThongTinThamKhao> lstTTTK = new List<ThongTinThamKhao>();
                if(hoso.LstKinhNghiemLamViec != null)
                    lstKNLV = hoso.LstKinhNghiemLamViec;

                if(hoso.LstHocVanBangCap != null)
                    lstHVBC = hoso.LstHocVanBangCap;
                if(hoso.LstThongTinThamKhao != null)
                    lstTTTK = hoso.LstThongTinThamKhao;

                #region Không có id và empid
                string strQuery = "update tblHoSoUngVien set "
                    + "LastName = '" + hoso.ThongTinLienHe.HoVaLot + "'"
                    + ", FirstName = '" + hoso.ThongTinLienHe.Ten + "'"
                    + ", NgaySinh = '" + hoso.ThongTinLienHe.NgaySinh.ToString("M/d/yyyy") + "'"
                    + ", GioiTinh = '" + hoso.ThongTinLienHe.GioiTinh.ToString() + "'"
                    + ", TinhTrangHonNhanID = '" + hoso.ThongTinLienHe.TinhTrangHonNhan.ID + "'"
                    + ", DiaChi = '" + hoso.ThongTinLienHe.DiaChi + "'"
                    + ", QuocGiaID = '" + hoso.ThongTinLienHe.QuocGia.ID + "'"
                    + ", TinhThanhID = '" + hoso.ThongTinLienHe.TinhThanh.ID + "'"
                    + ", QuanHuyenID = '" + hoso.ThongTinLienHe.QuanHuyen.ID + "'"
                    + ", SDT = '" + hoso.ThongTinLienHe.SDT + "'"
                    + ", Image = '" + hoso.ThongTinLienHe.PathImage + "'"
                    + ", SoNamKinhNghiem = " + hoso.NamKinhNghiem 
                    + ", ViTriMongMuon = '" + hoso.ViTriMongMuon + "'"
                    + ", ChucVuID = '" + hoso.ChucVu.ID + "'";

                if(hoso.LstNgoaiNgu != null)
                {
                    if(hoso.LstNgoaiNgu.Count>0)
                    {
                        strQuery += ", NgoaiNgu1ID = '" + hoso.LstNgoaiNgu[0].ID + "'"
                            + ", TrinhDoNN1ID = '" + hoso.LstTrinhDoNgoaiNgu[0].ID + "'";
                        if(hoso.LstNgoaiNgu.Count>1)
                        {
                            strQuery += ", NgoaiNgu2ID = '" + hoso.LstNgoaiNgu[1].ID + "'"
                                + ", TrinhDoNN2ID = '" + hoso.LstTrinhDoNgoaiNgu[2].ID + "'";

                            if(hoso.LstNgoaiNgu.Count>2)
                            {
                                strQuery += ", NgoaiNgu3ID = '" + hoso.LstNgoaiNgu[1].ID + "'"
                                    + ", TrinhDoNN3ID = '" + hoso.LstTrinhDoNgoaiNgu[2].ID + "'";
                            }
                        }
                    }             
                }
                if(hoso.LstNoiLamViec !=null)
                {
                    if (hoso.LstNoiLamViec.Count > 0)
                    {
                        strQuery += ", NoiLamViec1ID = '" + hoso.LstNoiLamViec[0].ID + "'";
                        if (hoso.LstNoiLamViec.Count > 1)
                        {
                            strQuery += ", NoiLamViec2ID = '" + hoso.LstNoiLamViec[1].ID + "'";
                            if(hoso.LstNoiLamViec.Count>2)
                                strQuery += ", NoiLamViec3ID = '" + hoso.LstNoiLamViec[2].ID + "'";
                        }
                    }
                    
                    
                }
                if(hoso.LstNganhNghe != null)
                {
                    if (hoso.LstNganhNghe.Count > 0)
                    {
                        strQuery += ", NganhNghe1ID = '" + hoso.LstNganhNghe[0].ID + "'";
                        if (hoso.LstNganhNghe.Count > 1)
                        {
                            strQuery += ", NganhNghe2ID = '" + hoso.LstNganhNghe[1].ID + "'";
                            if (hoso.LstNganhNghe.Count > 2)
                            {
                                strQuery += ", NganhNghe3ID = '" + hoso.LstNganhNghe[2].ID + "'";
                            }
                        }
                    }          
                    
                }
                strQuery += ", MucLuong = " + hoso.MucLuong
                    + ", IsChoPhepTimKiem = '" + hoso.IsChoPhepTimKiem.ToString() + "'"
                    + ", MucTieuNgheNghiep = '" + hoso.MucTieuNgheNghiep + "'"
                    + ", ThanhTichNoiBat = '" + hoso.MucTieuNgheNghiep + "'"
                    + ", KyNangNoiBat = '" + hoso.KyNangNoiBat + "'"
                    + ", NgayCapNhatGanNhat = '" + DateTime.Now.ToString("M/d/yyyy") + "'"
                    + "from tblHoSoUngVien where ID = '" + idHoSoUpdate + "'";
                sqlCommand.CommandText = strQuery;
                int result1 = sqlCommand.ExecuteNonQuery();
                int result2 = 0;
                #endregion            



                #region Lst KNLV
                if (lstKNLV != null && lstKNLV.Count>0)
                {
                    sqlCommand.CommandText = "delete from tblKinhNghiemLamViec where HoSoID = '" + idHoSoUpdate  + "'";
                    result2 = sqlCommand.ExecuteNonQuery();
                    for (int i = 0; i<lstKNLV.Count; i++)
                    {
                        sqlCommand.CommandText = String.Format(@"insert into tblKinhNghiemLamViec(
                            ID, HoSoID, ChucDanh, CongTy, ChucVuID, NganhNgheID, NgayBatDau, NgayKetThuc, NhiemVuThanhTich) 
                            values('{0}', '{1}', N'{2}', N'{3}', '{4}', '{5}', '{6}', '{7}', N'{8}')",
                       iKinhNghiemLamViecID++, idHoSoUpdate, lstKNLV[i].ChucDanh, lstKNLV[i].CongTy, lstKNLV[i].ChucVu.ID,
                       lstKNLV[i].NganhNghe.ID, lstKNLV[i].NgayBD.ToString("M/d/yyyy"), lstKNLV[i].NgayKT.ToString("M/d/yyyy"), lstKNLV[i].NhiemVuVaThanhTich);
                        sqlCommand.ExecuteNonQuery();
                    }
                }
                #endregion

                #region Lst HVBC
                if (lstHVBC != null && lstHVBC.Count >0)
                {
                    sqlCommand.CommandText = "delete from tblHocVanBangCap where HoSoID = '" + idHoSoUpdate + "'";
                    result2 = sqlCommand.ExecuteNonQuery();
                    for (int i = 0; i < lstHVBC.Count; i++)
                    {
                        sqlCommand.CommandText = String.Format(@"insert into tblHocVanBangCap(
                            ID, HoSoID, BangCapID, TruongID, ChuyenNganhID, NgayBatDau, NgayKetThuc, ThongTinLienQuan) 
                             values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', N'{7}')",
                            iHocVanBangCapID++, idHoSoUpdate, lstHVBC[i].BangCap.ID, lstHVBC[i].Truong.ID, lstHVBC[i].ChuyenNganh.ID,
                            lstHVBC[i].NgayBD.ToString("M/d/yyyy"), lstHVBC[i].NgayKT.ToString("M/d/yyyy"), lstHVBC[i].ThongTinLienQuan);
                        sqlCommand.ExecuteNonQuery();
                    }
                }
                #endregion

                #region Lst TTTK
                if (lstTTTK != null && lstTTTK.Count>0)
                {
                    sqlCommand.CommandText = "delete from tblThongTinThamKhao where HoSoID = '" + idHoSoUpdate + "'";
                    result2 = sqlCommand.ExecuteNonQuery();
                    for (int i = 0; i < lstHVBC.Count; i++)
                    {
                        sqlCommand.CommandText = String.Format(@"insert into tblThongTinThamKhao(
                            ID, HoSoID, HoTen, ChucDanh, CongTy, Email, SDT) 
                             values('{0}', '{1}', N'{2}', N'{3}', N'{4}', '{5}', '{6}')",
                        iThongTinThamKhaoID++, idHoSoUpdate, lstTTTK[i].HoTen, lstTTTK[i].ChucDanh, lstTTTK[i].CongTy, lstTTTK[i].Email, lstTTTK[i].SDT);
                        sqlCommand.ExecuteNonQuery();
                    }
                }
                #endregion


                sqlCommand.CommandText = String.Format(@"update tblIndex set ValueHoSo = {0}, ValueKinhNghiemLamViec = {1}, 
                 ValueHocVanBangCap = {2}, ValueThongTinThamKhao = {3}", iHoSoID, iKinhNghiemLamViecID, iHocVanBangCapID, iThongTinThamKhaoID);
                int result5 = sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch(Exception ex)
            {
            }

            return false;
        }
        #endregion

        #region
        private static Dictionary<char, char> dictionary = new Dictionary<char, char>
            {
                { 'á', 'a' },
                { 'à', 'a' },
                { 'ả', 'a' },
                { 'ã', 'a' },
                { 'ạ', 'a' },
                { 'â', 'a' },
                { 'ấ', 'a' },
                { 'ầ', 'a' },
                { 'ẩ', 'a' },
                { 'ẫ', 'a' },
                { 'ậ', 'a' },
                { 'ă', 'a' },
                { 'ắ', 'a' },
                { 'ằ', 'a' },
                { 'ẳ', 'a' },
                { 'ẵ', 'a' },
                { 'ặ', 'a' },
                { 'é', 'e' },
                { 'è', 'e' },
                { 'ẻ', 'e' },
                { 'ẽ', 'e' },
                { 'ẹ', 'e' },
                { 'ê', 'e' },
                { 'ế', 'e' },
                { 'ề', 'e' },
                { 'ể', 'e' },
                { 'ễ', 'e' },
                { 'ệ', 'e' },
                { 'í', 'i' },
                { 'ì', 'i' },
                { 'ỉ', 'i' },
                { 'ĩ', 'i' },
                { 'ị', 'i' },
                { 'ó', 'o' },
                { 'ò', 'o' },
                { 'ỏ', 'o' },
                { 'õ', 'o' },
                { 'ọ', 'o' },
                { 'ô', 'o' },
                { 'ố', 'o' },
                { 'ồ', 'o' },
                { 'ổ', 'o' },
                { 'ỗ', 'o' },
                { 'ộ', 'o' },
                { 'ơ', 'o' },
                { 'ớ', 'o' },
                { 'ờ', 'o' },
                { 'ở', 'o' },
                { 'ỡ', 'o' },
                { 'ợ', 'o' },
                { 'ú', 'u' },
                { 'ù', 'u' },
                { 'ủ', 'u' },
                { 'ũ', 'u' },
                { 'ụ', 'u' },
                { 'ư', 'u' },
                { 'ứ', 'u' },
                { 'ừ', 'u' },
                { 'ử', 'u' },
                { 'ữ', 'u' },
                { 'ự', 'u' },
                { 'ý', 'y' },
                { 'ỳ', 'y' },
                { 'ỷ', 'y' },
                { 'ỹ', 'y' },
                { 'ỵ', 'y' },
                { 'đ', 'd' },
            };

        public static string FormatInputData(string strInput)
        {
            string strOutput = String.Empty;
            try
            {
                string strLowerInput = strInput.ToLower();
                strLowerInput = strLowerInput.Trim();
                strLowerInput = Regex.Replace(strLowerInput, @"\s+", " ");
                foreach (char c in strLowerInput)
                {
                    if (dictionary.ContainsKey(c))
                    {
                        strOutput += dictionary[c];
                    }
                    else
                    {
                        strOutput += c;
                    }
                }
            }
            catch (System.Exception ex)
            {
                strOutput = String.Empty;
            }

            return strOutput;
        }
        
        public static void SearchCongViec(string key, string idTinhThanh, string idChucVu)
        {
            try
            {
                string strCommand = "SELECT LSJobTitleID,VNName,GioiThieu,MoTa,YeuCau,NguoiLienHe,NoiLamViecID,LSChucVuID,NgonNguID,NgayBD,NgayKT FROM LS_tblJobTitle WHERE ";                

                if (idTinhThanh != "")
                    strCommand += "NoiLamViecID='" + idTinhThanh + "' and ";

                if (idChucVu != "")
                    strCommand += "LSChucVuID='" + idChucVu + "' and ";

                if (idTinhThanh == "" && idChucVu == "")
                {
                    strCommand = strCommand.Remove(strCommand.Length - 7, 7);
                }
                else
                {
                    strCommand = strCommand.Remove(strCommand.Length - 5, 5);
                }                

                sqlCommand.CommandText = strCommand;
                SqlDataReader reader = sqlCommand.ExecuteReader();

                List<CongViec> lst = new List<CongViec>();
                while (reader.Read())
                {
                    DateTime ngayBD;
                    DateTime ngayKT;
                    try
                    {
                        ngayBD = DateTime.Parse(reader[9].ToString());
                    }
                    catch { ngayBD = DateTime.Today; }

                    try
                    {
                        ngayKT = DateTime.Parse(reader[10].ToString());
                    }
                    catch { ngayKT = DateTime.Today; }

                    CongViec congViec = new CongViec(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(),
                        reader[4].ToString(), reader[5].ToString(), reader[6].ToString(), reader[7].ToString(),
                        reader[8].ToString(), ngayBD, ngayKT);
                    lst.Add(congViec);
                }
                reader.Close();

                if (lst != null && lst.Count > 0)
                {
                    if (key != "")
                    {
                        key = Utility.FormatInputData(key);
                        Utility.LstCongViecCurrent = new List<CongViec>();
                        for (int i = 0; i < lst.Count; i++)
                        {
                            string str = Utility.FormatInputData(lst[i].Ten);
                            if (str.Contains(key))
                            {
                                Utility.LstCongViecCurrent.Add(lst[i]);
                            }
                        }
                    }
                    else
                    {
                        Utility.LstCongViecCurrent = new List<CongViec>(lst);
                    }
                }
            }
            catch
            {
                Console.Write("Loi");                
            }
        }
        #endregion   
      
    }
}
